package mwt.xml.xdbforms.dbms.query.impl;

import java.io.IOException;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathExpressionException;
import javax.xml.xpath.XPathFactory;
import mwt.xml.xdbforms.dbms.query.QueryManager;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;

public class MySQLQueryManager implements QueryManager {
    private Connection connection;

    public MySQLQueryManager (Connection connection) {
        this.connection = connection;
    }

    public boolean executeNonQuery(String sql) throws SQLException {
        Statement st = null;
        st = this.connection.createStatement();
        
        try {
            return st.execute(sql);
        } finally {
            if (st!=null) {
                st.close();
            }
        }
    }

    public boolean update(String xmlData) throws SQLException, ParserConfigurationException, SAXException, IOException, XPathExpressionException {
        DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
        dbf.setNamespaceAware(false);
        dbf.setValidating(false);

        XPathFactory xpf = XPathFactory.newInstance();
        XPath x = xpf.newXPath();

        DocumentBuilder builder = dbf.newDocumentBuilder();
        Document document = builder.parse(new InputSource(new StringReader(xmlData)));
        document.normalizeDocument();

        NodeList data = document.getElementsByTagName("data");
        NodeList children = data.item(0).getChildNodes();

        NodeList tableSchemaNl = document.getElementsByTagName("schema-name");
        String schemaName = tableSchemaNl.item(0).getTextContent();

        NodeList tableNameNl = document.getElementsByTagName("table-name");
        String tableName = tableNameNl.item(0).getTextContent();

        NodeList primaryKey = document.getElementsByTagName("primary-key");
        NodeList pkCols = primaryKey.item(0).getChildNodes();

        StringBuffer sb = new StringBuffer("UPDATE ");
        sb.append(schemaName);
        sb.append(".");
        sb.append(tableName);
        sb.append(" SET ");

        for(int i=0; i<children.getLength(); i++){
            Element elem = (Element) children.item(i);
            String nodeName = elem.getNodeName();//children.item(i).getNodeName();
            String nodeContent = elem.getTextContent();//children.item(i).getTextContent();
            
            if (!"schema-name".equals(nodeName) && !"table-name".equals(nodeName)
                    && !"primary-key".equals(nodeName) && !"foreign-key".equals(nodeName)) {
                String autoIncr = elem.getAttribute("autoincr");
                if ("false".equals(autoIncr)) {
                    if (nodeContent.contains("'")) {
                        nodeContent=nodeContent.replace("'","\\'");
                    }
                    sb.append(nodeName);
                    sb.append(" = '");
                    sb.append(nodeContent);
                    if (i!=children.getLength()-1) {
                        sb.append("',");
                    } else {
                        sb.append("'");
                    }
                }
            }
        }
        sb.append(" WHERE ");

        for (int i=0; i<pkCols.getLength(); i++) {
            Element col = (Element) pkCols.item(i);
            String colName = col.getTextContent();
           
            NodeList value = (NodeList) x.evaluate("/data//"+colName, document,XPathConstants.NODESET);
            Element elem =  (Element)value.item(0);
            String colText = elem.getTextContent();

            sb.append(colName);
            sb.append(" = '");
            sb.append(colText);

            if (i!=children.getLength()-1) {
                sb.append("'");
            } else {
                sb.append("',");
            }
        }

        //System.out.println("Query = "+sb.toString());
        return this.executeNonQuery(sb.toString());
    }

    public boolean insert(String xmlData) throws SQLException, ParserConfigurationException, SAXException, IOException {
        DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
        dbf.setNamespaceAware(false);
        dbf.setValidating(false);

        DocumentBuilder builder = dbf.newDocumentBuilder();
        Document document = builder.parse(new InputSource(new StringReader(xmlData)));
        document.normalizeDocument();

        NodeList data = document.getElementsByTagName("data");
        NodeList children = data.item(0).getChildNodes();
        
        NodeList tableSchemaNl = document.getElementsByTagName("schema-name");
        String schemaName = tableSchemaNl.item(0).getTextContent();

        NodeList tableNameNl = document.getElementsByTagName("table-name");
        String tableName = tableNameNl.item(0).getTextContent();

        //String useQuery = "USE " + schemaName;
        StringBuffer sb = new StringBuffer("INSERT INTO ");
        sb.append(schemaName);
        sb.append(".");
        sb.append(tableName);
        sb.append(" SET ");
        //String query = "INSERT INTO "+ schemaName +"."+ tableName+" SET ";
        for(int i=0; i<children.getLength(); i++){
            Element elem = (Element) children.item(i);
            String nodeName = elem.getNodeName();//children.item(i).getNodeName();
            String nodeContent = elem.getTextContent();//children.item(i).getTextContent();

            if (!"schema-name".equals(nodeName) && !"table-name".equals(nodeName)
                    && !"primary-key".equals(nodeName) && !"foreign-key".equals(nodeName)) {
                String autoIncr = elem.getAttribute("autoincr");
                if ("false".equals(autoIncr)) {
                    if (nodeContent.contains("'")) {
                        nodeContent=nodeContent.replace("'","\\'");
                    }
                    sb.append(nodeName);
                    sb.append(" = '");
                    sb.append(nodeContent);
                    if (i!=children.getLength()-1) {
                        sb.append("',");
                    } else {
                        sb.append("'");
                    }
                }
            }
        }

        //System.out.println("Query = "+sb.toString());
        return this.executeNonQuery(sb.toString());
    }

    public Set<List<String>> load(String schemaName, String tableName) throws SQLException {
        Set<List<String>> listResult = new HashSet<List<String>>();
        List<String> columns = null;

        Statement st = null;
        ResultSet rs = null;

        try {
            st = connection.createStatement();
            st.execute("USE "+schemaName);
            
            rs = st.executeQuery("SELECT * FROM " + tableName);
            int columnsCount = rs.getMetaData().getColumnCount();
            while (rs.next()) {
                columns = new ArrayList<String>();

                for (int i=1; i<=columnsCount; i++) {
                    String obj = rs.getString(i);
                    columns.add((obj == null) ? "" : obj);
                }
                listResult.add(columns);
            }
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch(SQLException ex) {}
            }

            if (st != null) {
                try {
                    st.close();
                } catch(SQLException ex) {}
            }
        }

        return listResult;
    }

    public Map<String, String> getRow(String schemaName, String tableName, String pkName, String pkValue) throws SQLException {
        Map<String, String> row = new HashMap<String, String>();

        Statement st = null;
        ResultSet rs = null;
        String query = "SELECT * FROM "+tableName;
        query += " WHERE "+pkName+" = '"+pkValue+"'";

        try {
            st = connection.createStatement();
            st.execute("USE "+schemaName);

            rs = st.executeQuery(query);
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnsCount = rsmd.getColumnCount();
            while (rs.next()) {
                for (int i=1; i<=columnsCount; i++) {
                    String obj = rs.getString(i);
                    row.put(rs.getMetaData().getColumnName(i), (obj == null) ? "" : obj);
                }
            }
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch(SQLException ex) {}
            }

            if (st != null) {
                try {
                    st.close();
                } catch(SQLException ex) {}
            }
        }

        return row;
    }

    public boolean deleteRow(String schemaName, String tableName, String pkName, String pkValue) throws SQLException {
        StringBuffer sql = new StringBuffer("DELETE FROM ");
        
        sql.append(schemaName);
        sql.append(".");
        sql.append(tableName);
        sql.append(" WHERE ");
        sql.append(pkName);
        sql.append(" = '");
        sql.append(pkValue);
        sql.append("'");
        
        return this.executeNonQuery(sql.toString());
    }
}
